﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
using System.Data.SqlClient;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace DAL
{
    public class InterviewDAL
    {
        Database db = DatabaseFactory.CreateDatabase("constring");
        /// <summary>
        /// 总数量
        /// </summary>
        /// <returns></returns>
        public int countinter(string interview_data)
        {

            string str = "select count(interview_data) from Interview where 1=1";
            if (interview_data != null && interview_data != "")
            {
                str += " and interview_data like ('%" + interview_data + "%') ";
            }
         

            int ds =Convert.ToInt32( db.ExecuteScalar(CommandType.Text, str));
            return ds;
            
        }

        ///// <summary>
        ///// 面试表显示数据
        ///// </summary>
        ///// <returns></returns>
        //public List<Interview> GetInter(int pageindex, int pagesize)
        //{
        //    try
        //    {
        //        if (pageindex < 1)
        //        {
        //            pageindex = 1;
        //        }
        //        int start = (pageindex - 1) * pagesize + 1;
        //        int end = pageindex * pagesize;
        //        DataSet ds = db.ExecuteDataSet(CommandType.Text, "select * from( select ROW_NUMBER() over (order by a.interview_id)i, a.interview_id,a.position_id,a.interview_data,a.interview_tapes,a.interview_image,a.interview_attached,a.interview_del,a.interview_integrity,b.position_name,r.Rated_Content,l.Login_Name from Interview a left join position b on a.position_id=b.position_id left join Rated r on a.Rated_Id=r.Rated_Id left join Login_User l on a.User_id=l.Login_Id where  interview_del='1') s where i between '" + start + "'  and '" + end + "'");
        //        List<Interview> list = new List<Interview>();
        //        foreach (DataRow dr in ds.Tables[0].Rows)
        //        {
        //            Interview Interview = new Interview();
        //            Interview.interview_attached = dr["interview_attached"].ToString();
        //            Interview.interview_data = dr["interview_data"].ToString();
        //            Interview.interview_del = Convert.ToInt32(dr["interview_del"]);
        //            Interview.interview_id = Convert.ToInt32(dr["interview_id"]);
        //            Interview.interview_image = dr["interview_image"].ToString();
        //            Interview.interview_integrity = dr["interview_integrity"].ToString();
        //            Interview.interview_tapes = dr["interview_tapes"].ToString();
        //            // Interview.position_id = Convert.ToInt32(dr["position_id"]);
        //            // Interview.Rated_Id = Convert.ToInt32(dr["Rated_Id"]);
        //            // Interview.User_id = Convert.ToInt32(dr["User_id"]);
        //            Interview.position_name = dr["position_name"].ToString();
        //            Interview.Login_Name = dr["Login_Name"].ToString();
        //            Interview.Rated_Content = dr["Rated_Content"].ToString();
        //            list.Add(Interview);
        //        }
        //        return list;
        //    }
        //    catch (Exception)
        //    {
        //        throw;
        //    }
        //}
        /// <summary>
        /// 面试表添加数据
        /// </summary>
        /// <param name="m">面试表</param>
        /// <returns></returns>
        public int SetInter(Interview Interview)
        {
            try
            {

                return db.ExecuteNonQuery(CommandType.Text, "insert into Interview(position_id,interview_data,interview_tapes,interview_image,interview_attached,interview_del,interview_integrity,Firm_Id,User_id,Rated_Id) values('" + Interview.position_id + "','" + Interview.interview_data + "','" + Interview.interview_tapes + "','" + Interview.interview_image + "','" + Interview.interview_attached + "','" + Interview.interview_del + "','" + Interview.interview_integrity + "','" + Interview.Firm_Id + "','" + Interview.User_id + "','" + Interview.Rated_Id + "')");
      
            }
            catch(Exception)
            {
                throw;
            }
        }
        /// <summary>
        /// 面试表删除数据
        /// </summary>
        /// <param name="m">面试表ID</param>
        /// <returns></returns>
        public int DelInter(int id)
        {
            try
            {
                int sss = 0;
                return db.ExecuteNonQuery(CommandType.Text, "update Interview set interview_del='"+sss+"' where interview_id=" + id);
                
            }
            catch(Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 根据ID显示数据
        /// </summary>
        /// <returns></returns>
        public List<Interview> GetInterUpdate(int id)
        {
            try
            {

                DataSet dbset = db.ExecuteDataSet(CommandType.Text, "select   Firm_Id,interview_attached,interview_data,interview_del,interview_id,interview_image,interview_integrity,interview_tapes,position_id,Rated_Id,User_id   from Interview  where interview_id=" + id);
                List<Interview> list = new List<Interview>();
               // DataTable Table = new DataTable();
                foreach (DataRow dr in dbset.Tables[0].Rows)
                {
                    Interview Interview = new Interview();
                    Interview.Firm_Id = Convert.ToInt32(dr["Firm_Id"]);
                    Interview.interview_attached = dr["interview_attached"].ToString();
                    Interview.interview_data = dr["interview_data"].ToString();
                    Interview.interview_del = Convert.ToInt32(dr["interview_del"]);
                    Interview.interview_id = Convert.ToInt32(dr["interview_id"]);
                    Interview.interview_image = dr["interview_image"].ToString();
                    Interview.interview_integrity = dr["interview_integrity"].ToString();
                    Interview.interview_tapes = dr["interview_tapes"].ToString();
                    Interview.position_id = Convert.ToInt32(dr["position_id"]);
                    Interview.Rated_Id = Convert.ToInt32(dr["Rated_Id"]);
                    Interview.User_id = Convert.ToInt32(dr["User_id"]);
                    list.Add(Interview);
                }
                return list;

            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 面试表修改数据
        /// </summary>
        /// <param name="m">面试表</param>
        /// <returns></returns>
        public int SetInterUpdate(Interview Interview)
        {
            try
            {

           
            return db.ExecuteNonQuery(CommandType.Text, "update Interview set position_id='"+Interview.position_id+"',interview_data='"+Interview.interview_data+"',interview_tapes='"+Interview.interview_tapes+"',interview_image='"+Interview.interview_image+"',interview_attached='"+Interview.interview_attached+"',interview_del='"+Interview.interview_del+"',interview_integrity='"+Interview.interview_integrity+"',Firm_Id='"+Interview.Firm_Id+"',User_id='"+Interview.User_id+"',Rated_Id='"+Interview.Rated_Id+"' where interview_id=" + Interview.interview_id);
      
            }
            catch(Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 根据名称显示数据
        /// </summary>
        /// <returns></returns>
        /// <summary>
        public List<Interview> GetInter(int pageindex, int pagesize, string interview_data)
        {
            try
            {

                int start = (pageindex - 1) * pagesize + 1;
                int end = pagesize * pageindex;

                string str = "select * from( select ROW_NUMBER() over (order by a.interview_id)i, a.interview_id,a.position_id,a.interview_data,a.interview_tapes,a.interview_image,a.interview_attached,a.interview_del,a.interview_integrity,b.position_name,r.Rated_Content,l.Login_Name from Interview a left join position b on a.position_id=b.position_id left join Rated r on a.Rated_Id=r.Rated_Id left join Login_User l on a.User_id=l.Login_Id where  interview_del=1";

                if (interview_data != null && interview_data != "")
                {
                    str += " and interview_data like ('%" + interview_data + "%') ";
                }

                str += "  )b where i between " + start + " and " + end + "";
                DataSet ds = db.ExecuteDataSet(CommandType.Text, str);

                List<Interview> list = new List<Interview>();

                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    Interview Interview = new Interview();
                    Interview.interview_attached = dr["interview_attached"].ToString();
                    Interview.interview_data = dr["interview_data"].ToString();
                    Interview.interview_del = Convert.ToInt32(dr["interview_del"]);
                    Interview.interview_id = Convert.ToInt32(dr["interview_id"]);
                    Interview.interview_image = dr["interview_image"].ToString();
                    Interview.interview_integrity = dr["interview_integrity"].ToString();
                    Interview.interview_tapes = dr["interview_tapes"].ToString();
                    // Interview.position_id = Convert.ToInt32(dr["position_id"]);
                    // Interview.Rated_Id = Convert.ToInt32(dr["Rated_Id"]);
                    // Interview.User_id = Convert.ToInt32(dr["User_id"]);
                    Interview.position_name = dr["position_name"].ToString();
                    Interview.Login_Name = dr["Login_Name"].ToString();
                    Interview.Rated_Content = dr["Rated_Content"].ToString();
                    list.Add(Interview);
                }
                return list;
            }
            catch (Exception)
            {
                throw;
            }

        }
    }
}
